﻿using Microsoft.Xrm.Sdk;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FFSupplier.Common.CustomException;
using FFSupplier.ServiceCore.Factory.Basic;
using FFSupplier.ServiceCore.Model;

namespace FFSupplier.ServiceCore.Impl.CrmConnection
{
    public abstract class CrmDynamicDBConnection
    {
        private SqlConnection connection;
        public CrmDynamicDBConnection()
        {
            connection = new SqlConnectionFactory().Create();
        }
        public List<PageEntityModel> ExecStoredProcedure(string spName, Hashtable commandParameters, params SqlParameter[] paras)
        {
            List<PageEntityModel> result = new List<PageEntityModel>();
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                if (paras != null)
                {
                    foreach (var para in paras)
                    {
                        command.Parameters.Add(para);
                    }
                }
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    result = AsEntityEnumerable(reader);
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        public List<PageEntityModel> ExecStoredProcedure(string spName, Hashtable commandParameters, out PageEntityConfigModel entityConfig)
        {
            List<PageEntityModel> result = new List<PageEntityModel>();
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    result = AsEntityEnumerable(reader, out entityConfig);
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        public int ExecStoredProcedureCount(string spName, Hashtable commandParameters)
        {
            int result = 0;
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        var count = reader[0];
                        if (count != DBNull.Value)
                        {
                            int.TryParse(count.ToString(), out result);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        public void ExecStoredProcedureNoQuery(string spName, Hashtable commandParameters)
        {
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 返回值-1：数据库插入失败；-2：数据重复；0：插入成功
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public int ExecStoredProcedureScalar(string spName, Hashtable commandParameters)
        {
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                object o = command.ExecuteScalar();
                if (o != DBNull.Value)
                {
                    return (int)o;
                }
                else
                {
                    return -1;
                }
            }
            catch (Exception e)
            {
                throw new SqlExecuteException(e);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command.
        /// </summary>
        /// <param name="command">the SqlCommand to be prepared</param>
        /// <param name="connection">a valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">a valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, Hashtable commandParameters)
        {
            //if the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            command.CommandTimeout = connection.ConnectionTimeout;
            if (commandParameters != null)
            {
                foreach (var key in commandParameters.Keys)
                {
                    if (commandParameters[key] is Guid)
                    {
                        Guid id = (Guid)commandParameters[key];
                        command.Parameters.AddWithValue(key.ToString(), Guid.Empty.Equals(id) ? DBNull.Value : commandParameters[key]);
                    }
                    else
                    {
                        command.Parameters.AddWithValue(key.ToString(), commandParameters[key] == null ? DBNull.Value : commandParameters[key]);
                    }
                }
            }
            return;
        }


        #region Entity Convert
        private List<PageEntityModel> AsEntityEnumerable(SqlDataReader reader)
        {
            DataTable table = new DataTable();
            table.Load(reader);
            PageEntityConfigModel config = new PageEntityConfigModel();
            config.ColumnConfig = table.Columns;
            return table.AsEnumerable().Select(row =>
            {
                return GenerateEntity(row, config);
            }).ToList();
        }
        private List<PageEntityModel> AsEntityEnumerable(SqlDataReader reader, out PageEntityConfigModel entityConfig)
        {
            DataTable table = new DataTable();
            table.Load(reader);
            PageEntityConfigModel config = new PageEntityConfigModel();
            config.ColumnConfig = table.Columns;
            entityConfig = config;
            return table.AsEnumerable().Select(row =>
            {
                return GenerateEntity(row, config);
            }).ToList();
        }

        protected PageEntityModel GenerateEntity(DataRow row, PageEntityConfigModel entityConfig)
        {
            PageEntityModel result = new PageEntityModel();
            result.Attributes = new Dictionary<string, object>();
            foreach (DataColumn col in entityConfig.ColumnConfig)
            {
                //switch (col.DataType.Name)
                //{
                //    case "Int64":
                //    case "Int32":
                //    case "Int16":

                //    case "string":
                //    case "String":
                //    default:
                //        result.Attributes.Add(col.ColumnName.ToLower(), GetCellStringValue(row[i]));
                //        break;
                //}
                object value = null;
                if (row[col.ColumnName] != DBNull.Value)
                {
                    value = row[col.ColumnName];
                }
                result.Attributes.Add(col.ColumnName, value);
            }
            return result;
        }

        protected string GetCellStringValue(object cell)
        {
            if (cell != DBNull.Value)
            {
                return cell.ToString();
            }
            return null;
        }
        protected Money GetCellMoneyValue(object cell)
        {
            Money result = new Money(0);
            if (cell != DBNull.Value)
            {
                decimal value;
                if (decimal.TryParse(cell.ToString(), out value))
                {
                    result = new Money(value);
                }
            }
            return result;
        }

        protected decimal GetCellDecimalValue(object cell)
        {
            decimal result = new decimal(0);
            if (cell != DBNull.Value)
            {
                decimal.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected int GetCellIntValue(object cell)
        {
            int result = 0;
            if (cell != DBNull.Value)
            {
                int.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected int? GetCellIntValueNullable(object cell)
        {
            int? result = null;
            int val = 0;
            if (cell != DBNull.Value)
            {
                int.TryParse(cell.ToString(), out val);
                result = val;
            }
            return result;
        }

        protected float GetCellFloatValue(object cell)
        {
            float result = new float();
            if (cell != DBNull.Value)
            {
                float.TryParse(cell.ToString(), out result);
            }
            return result;
        }

        protected double GetCellDoubleValue(object cell)
        {
            double result = new double();
            if (cell != DBNull.Value)
            {
                double.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected bool GetCellBoolValue(object cell)
        {
            bool result = false;
            if (cell != DBNull.Value)
            {
                bool.TryParse(cell.ToString(), out result);
            }
            return result;
        }

        protected bool? GetCellBoolValueNullable(object cell)
        {
            bool? result = null;
            bool val = false;
            if (cell != DBNull.Value)
            {
                bool.TryParse(cell.ToString(), out val);
                result = val;
            }
            return result;
        }
        protected Guid GetCellGuidValue(object cell)
        {
            Guid result = Guid.Empty;
            if (cell != DBNull.Value)
            {
                Guid.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected OptionSetValue GetCellOptionSetValue(object cell)
        {
            OptionSetValue result = null;
            if (cell != DBNull.Value)
            {
                int value;
                if (int.TryParse(cell.ToString(), out value))
                {
                    result = new OptionSetValue(value);
                }
            }
            return result;
        }
        protected EntityReference GetCellEntityReferenceValue(object cellId, string entityLogicalName)
        {
            EntityReference result = null;
            if (cellId != DBNull.Value)
            {
                Guid value;
                if (Guid.TryParse(cellId.ToString(), out value))
                {
                    result = new EntityReference()
                    {
                        Id = value,
                        LogicalName = entityLogicalName
                    };
                }
            }
            return result;
        }
        protected EntityReference GetCellEntityReferenceValue(object cellId, object cellName, string entityLogicalName)
        {
            EntityReference result = null;
            if (cellId != DBNull.Value)
            {
                Guid value;
                if (Guid.TryParse(cellId.ToString(), out value))
                {
                    result = new EntityReference()
                    {
                        Id = value,
                        Name = GetCellStringValue(cellName),
                        LogicalName = entityLogicalName
                    };
                }
            }
            return result;
        }

        protected E GetCellEnumValue<E>(object cell)
        {
            int val = GetCellIntValue(cell);
            if (Enum.IsDefined(typeof(E), val))
            {
                return (E)Enum.ToObject(typeof(E), val);
            }
            return (E)Enum.ToObject(typeof(E), int.MaxValue);
        }

        protected DateTime GetCellDateTimeValue(object cell)
        {
            DateTime result = DateTime.MinValue;
            if (cell != DBNull.Value)
            {
                DateTime.TryParse(cell.ToString(), out result);
            }
            return result;
        }
        protected DateTime? GetCellDateTimeValueNullable(object cell)
        {
            DateTime? result = null;
            DateTime val;
            if (cell != DBNull.Value)
            {
                DateTime.TryParse(cell.ToString(), out val);
                result = val;
            }
            return result;
        }
        #endregion
    }
}
